/*
 * Copyright (c) 2010 CCX(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCX(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCX(China) Co.,Ltd.
 *
 * Author: Wangyaoxi
 * Date: 2010-4-7 上午09:47:56
 */
package com.ccxe.manage.util.help;

import com.ccxe.manage.service.page.bean.PageBean;

/**
 * sql转换类
 * 
 * @author wyx
 * 
 */
public class SqlHelper {

	private static String LIKE = "%";
	private static String GT = ">";
	private static String GE = ">=";
	private static String LW = "<";
	private static String LE = "<=";
	private static String EQ = "=";
	private static String NEQ = "!=";

	private static String AddStr = "and";

	/**
	 * <p>
	 * <li>生成简单的count(*) sql;适用有一个from</li>
	 * <li>传" select * from index_composition where col1!='aaaa'"</li>
	 * <li>返回" select count(*) from index_composition where col1!='aaaa'"</li>
	 * </p>
	 * 
	 * @param sql
	 * @return
	 */
	public String buildPageCountSql(String sql) {
		String result = "";
		String[] strs = sql.split("from");
		result = " select count(*) from " + strs[1];
		System.out.println("count:" + result);
		return result;
	}

	/**
	 * 简单的分页sql
	 * 
	 * @param sql
	 *            要写 表名.* ;例select INDEX_DETAIL.* from index_detail where
	 *            INDEXCODE16='h11013.CSI' order by TRADEDATE3 desc
	 * @param pb
	 *            分页bean
	 * @return
	 */
	public String buildPageSql(String sql, PageBean pb) {
		String result = "";
		result = " select * from (";
		result += " select t.*,rownum rn from (";
		result += sql;
		result += " ) t ";
		result += " )";
		result += " where rn > " + pb.getShowData() * (pb.getNowPage() - 1);
		result += " and rn <= " + pb.getShowData() * pb.getNowPage();
		System.out.println("page:" + result);
		return result;
	}

	public String buildTableColumnSql(String tableName) {
		String sql = "select  COLUMN_NAME,DATA_TYPE,DATA_LENGTH "
				+ " from  user_tab_cols " + " where table_name=upper('"
				+ tableName + "')";
		return sql;
	}

	public String addCondition(String condition) {
		return AddStr + condition;
	}

	public String addBothLike(String value) {
		return LIKE + value + LIKE;
	}

	public String addLeftLike(String value) {
		return LIKE + value;
	}

	public String addRightLike(String value) {
		return value + LIKE;
	}

	public String addGT(String columnName, String value) {
		return columnName + GT + value;
	}

	public String addGE(String columnName, String value) {
		return columnName + GE + value;
	}

	public String addLW(String columnName, String value) {
		return columnName + LW + value;
	}

	public String addLE(String columnName, String value) {
		return columnName + LE + value;
	}

	public String addEQ(String columnName, String value) {
		return columnName + EQ + value;
	}

	public String addNotEQ(String columnName, String value) {
		return columnName + NEQ + value;
	}

}
